-- 创建性别枚举
CREATE TYPE gender_enum AS ENUM ('男', '女', '未知');

-- 创建用户表
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar VARCHAR(255),
    gender gender_enum NOT NULL DEFAULT '未知',
    phone VARCHAR(11) NOT NULL,
    email VARCHAR(100) NOT NULL,
    nick_name VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 为用户表添加注释
COMMENT ON TABLE users IS '系统用户表，存储所有用户的基本信息和登录凭证';
COMMENT ON COLUMN users.id IS '用户ID，自增主键，类型为BIGINT';
COMMENT ON COLUMN users.username IS '用户名，用于登录，唯一标识';
COMMENT ON COLUMN users.password_hash IS '密码哈希值，存储加密后的用户密码';
COMMENT ON COLUMN users.avatar IS '用户头像，用户展示用户的头像';
COMMENT ON COLUMN users.gender IS '用户性别，可选数据：男、女、未知';
COMMENT ON COLUMN users.phone IS '手机号码，用于身份验证和通知，唯一';
COMMENT ON COLUMN users.email IS '用户邮箱，用于身份验证和通知，唯一';
COMMENT ON COLUMN users.nick_name IS '用户昵称，用于显示';
COMMENT ON COLUMN users.is_active IS '用户是否激活，true表示激活，false表示禁用';
COMMENT ON COLUMN users.created_by IS '创建人，用户记录该条信息是谁创建的';
COMMENT ON COLUMN users.created_at IS '记录创建时间，带时区';
COMMENT ON COLUMN users.updated_at IS '记录最后更新时间，带时区';

-- 创建角色表
CREATE TABLE roles (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 为角色表添加注释
COMMENT ON TABLE roles IS '角色表，定义系统中的各种角色，如管理员、编辑等';
COMMENT ON COLUMN roles.id IS '角色ID，自增主键，类型为BIGINT';
COMMENT ON COLUMN roles.code IS '角色编码';
COMMENT ON COLUMN roles.name IS '角色名称，唯一标识一个角色';
COMMENT ON COLUMN roles.description IS '角色功能描述信息';
COMMENT ON COLUMN roles.created_by IS '创建人，用户记录该条信息是谁创建的';
COMMENT ON COLUMN roles.created_at IS '记录创建时间，带时区';
COMMENT ON COLUMN roles.updated_at IS '记录最后更新时间，带时区';

-- 创建菜单表
CREATE TABLE menu (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    icon VARCHAR(100) NOT NULL,
    description TEXT,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 为菜单表添加注释
COMMENT ON TABLE menu IS '权限表，定义系统中的具体权限项';
COMMENT ON COLUMN menu.id IS '权限ID，自增主键，类型为BIGINT';
COMMENT ON COLUMN menu.code IS '权限编码，唯一标识一个权限';
COMMENT ON COLUMN menu.name IS '权限名称，用于显示';
COMMENT ON COLUMN menu.icon IS '菜单图标';
COMMENT ON COLUMN menu.description IS '菜单描述';
COMMENT ON COLUMN menu.created_by IS '创建人，用户记录该条信息是谁创建的';
COMMENT ON COLUMN menu.created_at IS '记录创建时间，带时区';
COMMENT ON COLUMN menu.updated_at IS '记录最后更新时间，带时区';

-- 用户角色关联表（多对多）
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

-- 为用户角色关联表添加注释
COMMENT ON TABLE user_roles IS '用户角色关联表，建立用户与角色的多对多关系';
COMMENT ON COLUMN user_roles.user_id IS '用户ID，关联users表的id字段';
COMMENT ON COLUMN user_roles.role_id IS '角色ID，关联roles表的id字段';
COMMENT ON COLUMN user_roles.created_by IS '创建人，用户记录该条信息是谁创建的';
COMMENT ON COLUMN user_roles.created_at IS '关联记录创建时间，带时区';

-- 角色菜单关联表（多对多）
CREATE TABLE role_menu (
    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    menu_id BIGINT NOT NULL REFERENCES menu(id) ON DELETE CASCADE,
    created_by BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, menu_id)
);

-- 为角色菜单关联表添加注释
COMMENT ON TABLE role_menu IS '角色权限关联表，建立角色与权限的多对多关系';
COMMENT ON COLUMN role_menu.role_id IS '角色ID，关联roles表的id字段';
COMMENT ON COLUMN role_menu.menu_id IS '权限ID，关联menu表的id字段';
COMMENT ON COLUMN role_menu.created_by IS '创建人，用户记录该条信息是谁创建的';
COMMENT ON COLUMN role_menu.created_at IS '关联记录创建时间，带时区';

-- 创建索引提升查询性能
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);
CREATE INDEX idx_role_menu_role_id ON role_menu(role_id);
CREATE INDEX idx_role_menu_menu_id ON role_menu(menu_id);

-- 创建更新时间的触发器函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为各表添加更新时间触发器
CREATE TRIGGER update_users_modtime
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_roles_modtime
    BEFORE UPDATE ON roles
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_menu_modtime
    BEFORE UPDATE ON menu
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

-- 插入数据
INSERT INTO "public"."users" ("id", "username", "avatar", "password_hash", "phone", "email", "nick_name", "is_active", "created_by", "created_at", "updated_at", "gender") VALUES (1, 'Super', NULL, '$2b$12$Nsa7OkbHcd4Chi70rfxko.7Fwn5kN8v8OVfbZoc1Zjl6oluGjjE7q', '13882006101', 'zhang.xiangyu@foxmail.com', '超级管理员', 't', 1, '2025-08-31 19:21:35+00', '2025-09-02 07:30:46.139864+00', '男');
INSERT INTO "public"."roles" ("id", "code", "name", "description", "created_by", "created_at", "updated_at") VALUES (1, 'R_SUPER', '超级管理员', '超级管理员', 1, '2025-09-01 12:07:01.964975+00', '2025-09-01 12:07:01.964975+00');
INSERT INTO "public"."user_roles" ("user_id", "role_id", "created_by", "created_at") VALUES (1, 1, 1, '2025-09-01 12:07:19.674374+00');

